package billing.system.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DBSession {
	private final static String db_driver = "com.mysql.jdbc.Driver";
	
	private final static String db_host = "127.0.0.1";
	private final static Integer db_port = 3306;
	private final static String db_name = "test";
	private final static String db_encoding = "UTF8";
	
	private final static String db_url = "jdbc:mysql://"+db_host+":"+db_port+"/"+db_name+
			"?useUnicode=true&characterEncoding="+db_encoding+"&zeroDateTimeBehavior=convertToNull";
	private final static String db_user = "root";
	private final static String db_passwd = "123123";
	
	public Connection conn = null;
	static {
	    try {
            Class.forName(db_driver);
        } catch (Exception e) {
            throw new IllegalArgumentException(e);
        }
	}
	
	private DBSession(Connection conn) {
		this.conn = conn;
		try {
			this.conn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static DBSession openSession(){
		Connection result = null;
		try {
			result  = DriverManager.getConnection(db_url, db_user,db_passwd);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return new DBSession(result);
	}
	
	public void commit(){
		try {
			this.conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void close(){
		try {
			this.conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public Statement createStatement() {
		try {
			return this.conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}


	/**
	 * 打印结果集
	 * @author：zenglb
	 * @createDate 2012-12-4 下午12:59:55
	 * @param resultSet
	 * @param isShowDesc
	 * @param isShowData
	 * @throws SQLException
	 */
	public static String printResult(ResultSet resultSet,boolean isShowDesc,boolean isShowData) throws SQLException{
		StringBuffer result = new StringBuffer();
		ResultSetMetaData rsmd = resultSet.getMetaData() ;
		int len = rsmd.getColumnCount();
		String[] columns = new String[len];
		String[] types = new String[len];
		String[] tables = new String[len];
		
		String[] ColumnClassNames = new String[len];
		Integer[] ColumnDisplaySizes = new Integer[len];
		
		for (int i = 0; i < len; i++) {
			columns[i] = rsmd.getColumnName(i+1);
			types[i] = rsmd.getColumnTypeName(i+1); 
			tables[i] = rsmd.getTableName(i+1);
			ColumnClassNames[i] =  rsmd.getColumnClassName(i+1);
			ColumnDisplaySizes[i] = rsmd.getColumnDisplaySize(i+1);
		}
		StringBuffer desc = new StringBuffer();
		int descLen = 0;
		if(isShowDesc){
			String format = "%-"+getFormatLen("DBName",tables)+"s | %-"+getFormatLen("ColumnName",columns)+"s | %-"+
					getFormatLen("ColumnType",types,ColumnDisplaySizes, 2)+"s | %-"+getFormatLen("JavaType",ColumnClassNames)+"s";
			String title = String.format(format,"DBName","ColumnName","ColumnType","JavaType");
			descLen = title.length();
			desc.append(title + "\n");
			desc.append(getStringBySize("-",descLen) +"\n");
			for (int i = 0; i < len; i++) {
				desc.append(String.format(format,tables[i],columns[i],types[i]+"("+ColumnDisplaySizes[i]+")",ColumnClassNames[i]) + "\n");
			}
			desc.append(getStringBySize("=",descLen) + "\n");
		}
		StringBuffer data = new StringBuffer();
		int dataLen = 0;
		if(isShowData){
			@SuppressWarnings("unchecked")
			ArrayList<Object>[] resultLst = new ArrayList[len];
			int dataSize = 0;
			while(resultSet.next()){
				dataSize ++;
				for (int i = 0; i < len; i++) {
					if(null ==resultLst[i]){
						resultLst[i]= new ArrayList<Object>();
					}
					resultLst[i].add(resultSet.getObject(i+1)); 
				}
			}
			StringBuffer sbFormat = new StringBuffer (100);
			for (int i = 0; i < len; i++) {
				sbFormat.append("%-" +getFormatLen(columns[i],resultLst[i].toArray())+ "s | ");
			}
			Object[] colNames = columns;
			String title = String.format(sbFormat.toString(),colNames);
			data.append(title + "\n");
			dataLen = title.length();
			data.append(getStringBySize("-",dataLen) +"\n");
			Object[] rows = null;
			for (int i = 0; i < dataSize; i++) {
				rows = new Object[len];
				for (int j = 0; j < len; j++) {
					rows[j] = resultLst[j].get(i);
				}
				data.append(String.format(sbFormat.toString(),rows) + "\n");
			}
			data.append(getStringBySize("=",dataLen) +"\n");
		}
		int titleMaxLen = dataLen > descLen? dataLen:descLen;
		if(titleMaxLen > 0 ){
			result.append(getStringBySize("=",(titleMaxLen-19 )/2)+" Result Set Report "+getStringBySize("=",(titleMaxLen-19 )/2) +"\n");
			if(descLen > 0){
				result.append(desc);
			}
			if(dataLen > 0){
				result.append(data);
			}
			data.append(getStringBySize("=",titleMaxLen) +"\n");
		}
		System.out.println(result.toString());
		return result.toString();
	}
	
	private static String getStringBySize(String string, int descLen) {
		StringBuffer result = new StringBuffer(descLen );
		for (int i = 0; i < descLen; i++) {
			result.append(string);
		}
		return result.toString();
	}

	private static int getFormatLen(String colName,Object[] params){
		int size = colName.length();
		for (Object object : params) {
			int tmp = 0 ;
			if(null != object){
				String str = object.toString();
				tmp = str.length();
			}
			size = size >= tmp ? size : tmp;
		}
		return size + 2 ;
	}
	
	private static int getFormatLen(String colName,Object[] params,Object[] secParams,int len){
		int size = colName.length();
		int arrSize = params.length;
		for (int i = 0; i < arrSize; i++) {
			String str = params[i].toString() + secParams[i].toString();
			int tmp = str.length() + len;
			size = size >= tmp ? size : tmp;
		}
		return size + 2 ;
	}
	
	
	/**
	 * 展示sql的结果.
	 * @author：zenglb
	 * @createDate 2012-12-4 下午1:25:38
	 * @param sql
	 * @param isShowDesc
	 * @param isShowData
	 * @throws SQLException
	 */
	public static void showResultBySql(String sql,boolean isShowDesc,boolean isShowData) throws SQLException{
		DBSession session = DBSession.openSession();
		ResultSet resultSet =  null;
		Statement stmt = session.createStatement();
		resultSet = stmt.executeQuery(sql);
		DBSession.printResult(resultSet,isShowDesc,isShowData);
		resultSet.close();
		stmt.close();
		session.close();
	}
	public static void showResultBySql(String sql) throws SQLException{
		showResultBySql(sql, true, true);
	}
	
	public static void main(String[] args) throws SQLException {
//		DBSession.showResultBySql("select * from user", false, false);
		DatabaseMetaData databaseMetaData = openSession().conn.getMetaData();
		// 获取所有表
		// ResultSet tableSet = databaseMetaData.getTables(null, "%", "%",
		// new String[]{"TABLE"});
		// 获取tableName表列信息
		ResultSet columnSet = databaseMetaData.getColumns(null, "%","user", "%");
		//获取所有表 
		//ResultSet tableSet = databaseMetaData.getTables(null, "%", "%", new String[]{"TABLE"}); 
		//获取tableName表列信息 
		//ResultSet columnSet = databaseMetaData.getColumns(null, "%", tableName, "%"); 
		printResult(columnSet, true, true);
	}
}
